package com.easyhome.report.web;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.easyhome.report.common.utils.ColumnName;
import com.easyhome.report.common.utils.SpringUtils;
import com.easyhome.report.common.utils.Table;
import com.easyhome.report.common.utils.UploadUtils;
import com.easyhome.report.common.utils.excel.ExcelImport;
import com.easyhome.report.dao.ComBasicInfoDao;
import com.easyhome.report.dao.CommonDao;
import com.easyhome.report.entry.ComBasicInfoEntry;
import com.easyhome.report.entry.CommonEntry;
import com.easyhome.report.entry.PublicInfo;
import com.easyhome.report.entry.ReportEntry;
import com.easyhome.report.entry.Result;
import com.easyhome.report.service.ReportWorkCommonService;
import com.easyhome.report.service.impl.ReportService;

@Controller
public class ReportControl {

	@Autowired
	private ReportService reportService;
	@Autowired
	private CommonDao commonDao;
	@Autowired
	private ComBasicInfoDao comBasicInfoDao;
	
	private Map<String, ReportEntry> reportEntryMap;
	private Map<String,ComBasicInfoEntry> comBasicInfoEntryMap;
	
	@RequestMapping("/importExcel")
	public String importExcel(@RequestParam(value = "filename") MultipartFile file,@RequestParam("estaCom") String estaCom,
			@RequestParam("estaPer") String estaPer,@RequestParam("estaDate") String estaDate,@RequestParam("ledgerId") int ledgerId,
			@RequestParam("versionId") String versionId,
		RedirectAttributes redirectAttributes, HttpServletRequest request) throws Exception {
		PublicInfo info = new PublicInfo();
		info.setEstaCom(estaCom);
//		SimpleDateFormat format = new SimpleDateFormat("yyyy-dd");
//		Date date = format.parse("2018-05");
		info.setEstaDate(estaDate);
		info.setEstaPer(estaPer);
		info.setCreateTime(new Date());
		info.setVersionId(versionId);
		//账套默认值  2021
		info.setLedgerId(ledgerId);
		File tempfile = UploadUtils.upload(file);
		if (file == null) {
			redirectAttributes.addFlashAttribute("message", "文件不能为空");
		}

		InputStream is = null;
		Workbook wb = null;
		try {
			String filename = tempfile.getName();
			is = new FileInputStream(tempfile);
			if (ExcelImport.isExcel2007(filename)) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = new HSSFWorkbook(is);
			}
			int num = wb.getNumberOfSheets();
			//reportEntryMap
			reportEntryMap = this.reportService.findCategory();
			//comBasicInfoMap
			comBasicInfoEntryMap = this.comBasicInfoDao.findComBasicInfo();
			
			ArrayList<Result<T>> list = new ArrayList<>();
			for (int i = 2; i < num ; i++) {
				String name = wb.getSheetName(i);
				ReportEntry reportEntry = reportEntryMap.get(name.trim());
				Result<T> result = new Result<T>();
				if (reportEntry == null) {
					result.setSuccessed(false);
					result.setSheetName(name);
					result.getErrmessage().add("该工作簿编码不存在");
					list.add(result);
					continue;
				}
				String code = reportEntry.getCode();
				Sheet sheet = wb.getSheetAt(i);
				result = excuteReportForOne(sheet, info, code);
				list.add(result);
			}

			redirectAttributes.addFlashAttribute("message", list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					is = null;
					e.printStackTrace();
				}
			}
			if (wb != null) {
				wb.close();
			}
			if(tempfile.exists()) {
				tempfile.delete();  
			}
		}
		
		return "redirect:/";
	}

	private Result<T> excuteReportForOne(Sheet sheet,PublicInfo publicInfo,String serviceCode){
		Result<T> result = new Result<>();
		result.setSheetName(sheet.getSheetName());
		boolean b = SpringUtils.getApplicationContext().containsBean(serviceCode+"ReportService");
		if(!b) {
			result.setSuccessed(false);
			result.getErrmessage().add("没有该工作簿的解析器");
			return result;
		}
		try {
			ReportWorkCommonService reportWorkCommenService=(ReportWorkCommonService)SpringUtils.getBean(serviceCode + "ReportService");
			List<Object> entryList = new LinkedList<>();
			reportWorkCommenService.excute(sheet, entryList);
			//reportWorkCommenService.excute(result, sheet, publicInfo);
			this.persistEntrys(entryList,publicInfo,sheet);
		} catch (Exception e) {
			e.printStackTrace();
			result.setSuccessed(false);
			result.getErrmessage().add(e.getMessage());
			return result;
		}
		return result;
	}
	
	@Transactional
	private void persistEntrys(List<Object> entryList,PublicInfo publicInfo,Sheet sheet ) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException{
		
		ReportEntry reportEntry = reportEntryMap.get(sheet.getSheetName().trim());
		String reportType = reportEntry.getType();
		ComBasicInfoEntry comBasicInfoEntry  = comBasicInfoEntryMap.get(publicInfo.getEstaCom());
		boolean isEBS = comBasicInfoEntry.getIsEBS();
		
		for(Object entry: entryList){
			CommonEntry commonEntry = (CommonEntry)entry;
			/*
			 * set publicInfo
			 */
			commonEntry.setLedgerId(publicInfo.getLedgerId());
			commonEntry.setEstaCom(publicInfo.getEstaCom());
			commonEntry.setEstaDate(publicInfo.getEstaDate());
			commonEntry.setEstaPer(publicInfo.getEstaPer());
			commonEntry.setVersionId(publicInfo.getVersionId());
			commonEntry.setCreateTime( publicInfo.getCreateTime());
			/*
			 * delete
			 */
			Table table = entry.getClass().getAnnotation(Table.class);
		
			if(isEBS&&("J".equals(reportType)||"M".equals(reportType))){
				commonEntry.setTableName(table.value());
				commonDao.deleteData(commonEntry);
			}else if(!isEBS&&"J".equals(reportType)){
				commonEntry.setTableName(table.value());
				commonDao.deleteData(commonEntry);
			}else if(!isEBS&&"E".equals(reportType)){
				commonEntry.setTableName("EBS_"+table.value());
				commonDao.deleteData(commonEntry);
			}else if(!isEBS&&"M".equals(reportType)){
				commonEntry.setTableName(table.value());
				commonDao.deleteData(commonEntry);
				commonEntry.setTableName("EBS_"+table.value());
				commonDao.deleteData(commonEntry);
			}
		}
		
		for(Object entry: entryList){
			CommonEntry commonEntry = (CommonEntry)entry;
			/*
			 * save entry
			 */
			Table table = entry.getClass().getAnnotation(Table.class);
			String clauseSuffix = 	this.generateColumnsClaus(commonEntry);
			
			if(isEBS&&("J".equals(reportType)||"M".equals(reportType))){
				String clause = "INSERT INTO `FINA_REPORT_BI`.`"+table.value()+"` (";
				commonEntry.setSql(clause+clauseSuffix);
				commonDao.saveData(commonEntry);
			}else if(!isEBS&&"J".equals(reportType)){
				String clause = "INSERT INTO `FINA_REPORT_BI`.`"+table.value()+"` (";
				commonEntry.setSql(clause+clauseSuffix);
				commonDao.saveData(commonEntry);
			}else if(!isEBS&&"E".equals(reportType)){
				String clauseEbs = "INSERT INTO `FINA_REPORT_BI`.`"+"EBS_"+table.value()+"` (";
				commonEntry.setSql(clauseEbs+clauseSuffix);
				commonDao.saveData(commonEntry);
			}else if(!isEBS&&"M".equals(reportType)){
				String clause = "INSERT INTO `FINA_REPORT_BI`.`"+table.value()+"` (";
				commonEntry.setSql(clause+clauseSuffix);
				commonDao.saveData(commonEntry);
				String clauseEbs = "INSERT INTO `FINA_REPORT_BI`.`"+"EBS_"+table.value()+"` (";
				commonEntry.setSql(clauseEbs+clauseSuffix);
				commonDao.saveData(commonEntry);
			}
			
		}
		
	}
	
	private String generateColumnsClaus(Object entry) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException{
	
		//String columnsPlaus = "INSERT INTO `FINA_REPORT_BI`.`"+tableName+"` (";
		String columnsPlaus = "";
		String ValuesPlaus = " VALUES (";
		
		Field[] fields = entry.getClass().getDeclaredFields();
		for(Field field:fields){
			ColumnName columnName = entry.getClass().getDeclaredField(field.getName()).getDeclaredAnnotation(ColumnName.class);
			if(columnName!=null ){
				String columnNameValue = columnName.value();
				columnsPlaus+="`"+columnNameValue+"`,";
				
				//columnValue
				field.setAccessible(true);
				Object value = field.get(entry);
				if(value == null){
					ValuesPlaus += "null,";
				}else if(value instanceof String){
					ValuesPlaus += "\""+value+"\",";
				}else if(value instanceof Boolean){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Integer){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Double){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Date){
					ValuesPlaus += "\""+new Timestamp(((Date)value).getTime())+"\",";
				}
			
			}
		}
		
		Field[] supperFields = entry.getClass().getSuperclass().getDeclaredFields();
		for(Field field:supperFields){
			ColumnName columnName = entry.getClass().getSuperclass().getDeclaredField(field.getName()).getDeclaredAnnotation(ColumnName.class);
			if(columnName!=null ){
				String columnNameValue = columnName.value();
				columnsPlaus+="`"+columnNameValue+"`,";
			
				//columnValue
				field.setAccessible(true);
				Object value = field.get(entry);

				if("YCID".equals(columnNameValue)&& value==null){
					value = "";
				}
				
				if(value == null){
					ValuesPlaus += "null,";
				}else if(value instanceof String){
					ValuesPlaus += "\""+value+"\",";
				}else if(value instanceof Boolean){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Integer){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Double){
					ValuesPlaus += ""+value+",";
				}else if(value instanceof Date){
					ValuesPlaus += "\""+new Timestamp(((Date)value).getTime())+"\",";
				}else{
					ValuesPlaus += "null,";
				}
			
			}
		}
		
		columnsPlaus = columnsPlaus.substring(0, columnsPlaus.length()-1)+")";
		columnsPlaus+= ValuesPlaus.substring(1, ValuesPlaus.length()-1)+")";
		
		return columnsPlaus;
	}
   
}
